package id.net.iconpln.bb.server.dao;

import id.net.iconpln.bb.server.util.CommonModule;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;

import oracle.jdbc.OracleTypes;

import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

@Repository
public class RakorDao {
	@Autowired	private SessionFactory sessionFactory;
	
	public List<Map> insertBbPengendalian(String kode, Map map){
		List<Map> lMap = new ArrayList<Map>();
		Session session = sessionFactory.getCurrentSession();
        Connection con = session.connection();
        String sql = null;
        CallableStatement call;
        try {
            
            if (kode.equals("RAKOR")){
            	if (map.get("crud").toString().equals("UPDATE")){
	            	sql = "{call bbtrxpengendalian.SETNO_RAKOR(?)}";
	            	CallableStatement norakor;
	            	norakor = con.prepareCall(sql);
	            	norakor.setString(1, map.get("norakor").toString());
	            	norakor.execute();
	                
	                sql = "{call bbtrxpengendalian.SETUPDATEBY(?)}";
	                CallableStatement updateby;
	                updateby = con.prepareCall(sql);
	                updateby.setString(1, map.get("createby").toString());
	                updateby.execute();
	                
	            }
	            if (map.get("crud").toString().equals("DELETE")){
	                	sql = "{call bbtrxpengendalian.SETNO_RAKOR(?)}";
	                	System.out.println("no rakor: " + map.get("norakor").toString());
	                	CallableStatement norakordel;
	                	norakordel = con.prepareCall(sql);
	                	norakordel.setString(1, map.get("norakor").toString());
	                	norakordel.execute();
	            }
                
                sql = "{call bbtrxpengendalian.SETAGENDA(?)}";
                call = con.prepareCall(sql);
                call.setString(1, map.get("agenda").toString());
                call.execute();
                
                sql = "{call bbtrxpengendalian.SETTEMPAT(?)}";
                call = con.prepareCall(sql);
                call.setString(1, map.get("tempat").toString());
                call.execute();
                
                sql = "{call bbtrxpengendalian.SETALAMAT(?)}";
                call = con.prepareCall(sql);
                call.setString(1, map.get("alamat").toString());
                call.execute();
                
                sql = "{call bbtrxpengendalian.SETTANGGAL(?)}";
                call = con.prepareCall(sql);
                DateFormat df = new SimpleDateFormat("dd/mm/yyyy");
                Date tgl = df.parse(map.get("tanggal").toString());
                call.setDate(1, new java.sql.Date(tgl.getTime()));
                call.execute();
                
                sql = "{call bbtrxpengendalian.SETKETERANGAN(?)}";
                call = con.prepareCall(sql);
                call.setString(1, map.get("ket").toString());
                call.execute();
                
                sql = "{call bbtrxpengendalian.SETCREATEBY(?)}";
                call = con.prepareCall(sql);
                call.setString(1, map.get("createby").toString());
                call.execute();
                
                sql = "{call bbtrxpengendalian.SETAPP_ACCESS(?)}";
                call = con.prepareCall(sql);
                call.setString(1, map.get("app_access").toString());
                call.execute();
            }
            
            if (kode.equals("RAKORKONFIRM")){
        		sql = "{call bbtrxpengendalian.SETNO_RAKOR(?)}";
        		CallableStatement rakorkonfirm;
        		rakorkonfirm = con.prepareCall(sql);
        		rakorkonfirm.setString(1, map.get("norakor").toString());
        		rakorkonfirm.execute();
        	
        		sql = "{call bbtrxpengendalian.SETAGENDA(?)}";
        		rakorkonfirm = con.prepareCall(sql);
        		rakorkonfirm.setString(1, map.get("agenda").toString());
        		rakorkonfirm.execute();
                
                sql = "{call bbtrxpengendalian.SETTEMPAT(?)}";
                rakorkonfirm = con.prepareCall(sql);
                rakorkonfirm.setString(1, map.get("tempat").toString());
                rakorkonfirm.execute();
                
                sql = "{call bbtrxpengendalian.SETALAMAT(?)}";
                rakorkonfirm = con.prepareCall(sql);
                rakorkonfirm.setString(1, map.get("alamat").toString());
                rakorkonfirm.execute();
                
                sql = "{call bbtrxpengendalian.SETTANGGAL(?)}";
                rakorkonfirm = con.prepareCall(sql);
                rakorkonfirm.setString(1, map.get("tanggal").toString());
                rakorkonfirm.execute();
                
                sql = "{call bbtrxpengendalian.SETKETERANGAN(?)}";
                rakorkonfirm = con.prepareCall(sql);
                rakorkonfirm.setString(1, map.get("ket").toString());
                rakorkonfirm.execute();
                
                sql = "{call bbtrxpengendalian.SETCREATEBY(?)}";
                rakorkonfirm = con.prepareCall(sql);
                rakorkonfirm.setString(1, map.get("createby").toString());
                rakorkonfirm.execute();
                
                sql = "{call bbtrxpengendalian.SETAPP_ACCESS(?)}";
                rakorkonfirm = con.prepareCall(sql);
                rakorkonfirm.setString(1, map.get("app_access").toString());
                rakorkonfirm.execute();
                
                sql = "{call bbtrxpengendalian.SETID_PLTU(?)}";
                rakorkonfirm = con.prepareCall(sql);
                rakorkonfirm.setInt(1, Integer.parseInt(map.get("idpltu").toString()));
                rakorkonfirm.execute();
                
                sql = "{call bbtrxpengendalian.SETID_PEMASOK(?)}";
                rakorkonfirm = con.prepareCall(sql);
                rakorkonfirm.setInt(1, Integer.parseInt(map.get("idpemasok").toString()));
                rakorkonfirm.execute();
            }
            
            if (kode.equals("RAKOR_PESERTA") || kode.equals("RAKOR_PESERTA_KONFIRM")){
            	sql = "{call bbtrxpengendalian.SETNO_RAKOR(?)}";
                call = con.prepareCall(sql);
                call.setString(1, map.get("no_rakor").toString());
                call.execute();
                
                sql = "{call bbtrxpengendalian.SETID_PLTU(?)}";
                call = con.prepareCall(sql);
                call.setString(1, map.get("id_pltu").toString());
                call.execute();
                
                sql = "{call bbtrxpengendalian.SETSTATUS(?)}";
                call = con.prepareCall(sql);
                call.setString(1, map.get("status").toString());
                call.execute();
                
                sql = "{call bbtrxpengendalian.SETID_PEMASOK(?)}";
                call = con.prepareCall(sql);
                call.setString(1, map.get("id_pemasok").toString());
                call.execute();
                
                sql = "{call bbtrxpengendalian.SETCREATEBY(?)}";
                call = con.prepareCall(sql);
                call.setString(1, map.get("createby").toString());
                call.execute();
                
                sql = "{call bbtrxpengendalian.SETAPP_ACCESS(?)}";
                call = con.prepareCall(sql);
                call.setString(1, map.get("app_access").toString());
                call.execute();
            }
            
            sql = "{? = call BBTRXPENGENDALIAN.INSERTBBPENGENDALIAN(?,?)}";
            call = con.prepareCall(sql);
            call.registerOutParameter(1, OracleTypes.VARCHAR);
            call.setString(2, kode);
            System.out.println("crud: " + map.get("crud").toString());
            call.setString(3, map.get("crud").toString());            
            call.execute();
            System.out.println("call.getString(1) : " + call.getString(1));
            map.clear();
            map.put("message", call.getString(1));             
        }catch (Exception e) {
			e.printStackTrace();
			map.clear();
            map.put("message", e.getMessage());  
		}
        lMap.add(map);
        return lMap;
	}
	
	public List<Map> getListPengendalianBb(String kode, Map map){
		List<Map> lMap = new ArrayList<Map>();
		Session session = sessionFactory.getCurrentSession();
        Connection con = session.connection();
        System.out.println("kode: " + kode);
        String sql = null;
        CallableStatement call; 
        try {
        	
        	if (kode.equals("RAKORKONFIRM")){
            	sql = "{call bbtrxpengendalian.SETNO_RAKOR(?)}";
            	System.out.println("no rakor: " + map.get("norakor").toString());
            	call = con.prepareCall(sql);
            	call.setString(1, map.get("norakor").toString());
            	call.execute();
        	}else if (kode.equals("RAKOR_BY_UNIT")){
        		sql = "{call bbtrxpengendalian.SETid_pltu(?)}";
            	call = con.prepareCall(sql);
            	call.setString(1, map.get("id_pltu").toString());
            	call.execute();
            	
            	sql = "{call bbtrxpengendalian.SETid_pemasok(?)}";
            	call = con.prepareCall(sql);
            	call.setString(1, map.get("id_pemasok").toString());
            	call.execute();
        	}
        	
            sql = "{? = call bbtrxpengendalian.GETLISTPENGENDALIANBY(?)}";
            call = con.prepareCall(sql);
            call.registerOutParameter(1, OracleTypes.CURSOR);
            call.setString(2, kode);
            call.execute();
            ResultSet rset = (ResultSet) call.getObject(1);
            lMap = CommonModule.convertResultsetToListLower(rset);
           }catch (Exception e) {
			e.printStackTrace();
		}
        
        return lMap;
	}
	
	public List<Map> getListpemasok_rakor(String kode, Map map){
		List<Map> lMap = new ArrayList<Map>();
		Session session = sessionFactory.getCurrentSession();
        Connection con = session.connection();
        System.out.println("kode: " + kode);
        String sql = null;
        CallableStatement call; 
        try {
        	
        		sql = "{call bbtrxpengendalian.SETID_PLTU(?)}";
            	System.out.println("no rakor: " + map.get("id_pltu").toString());
            	call = con.prepareCall(sql);
            	call.setString(1, map.get("id_pltu").toString());
            	call.execute();
            	
            sql = "{? = call bbtrxpengendalian.GETLISTPENGENDALIANBY(?)}";
            call = con.prepareCall(sql);
            call.registerOutParameter(1, OracleTypes.CURSOR);
            call.setString(2, kode);
            call.execute();
            ResultSet rset = (ResultSet) call.getObject(1);
            lMap = CommonModule.convertResultsetToListLower(rset);
           }catch (Exception e) {
			e.printStackTrace();
		}
        
        return lMap;
	}
	
	public List<Map> getListpltu_rakor(String kode, Map map){
		List<Map> lMap = new ArrayList<Map>();
		Session session = sessionFactory.getCurrentSession();
        Connection con = session.connection();
        System.out.println("kode: " + kode);
        String sql = null;
        CallableStatement call; 
        try {
        	
        		sql = "{call bbtrxpengendalian.SETNO_RAKOR(?)}";
            	System.out.println("no rakor: " + map.get("no_rakor").toString());
            	call = con.prepareCall(sql);
            	call.setString(1, map.get("no_rakor").toString());
            	call.execute();
        	
            sql = "{? = call bbtrxpengendalian.GETLISTPENGENDALIANBY(?)}";
            call = con.prepareCall(sql);
            call.registerOutParameter(1, OracleTypes.CURSOR);
            call.setString(2, kode);
            call.execute();
            ResultSet rset = (ResultSet) call.getObject(1);
            lMap = CommonModule.convertResultsetToListLower(rset);
           }catch (Exception e) {
			e.printStackTrace();
		}
        
        return lMap;
	}
	
	public List<Map> setRelationRakor(String kode, Map map){
		List<Map> lMap = new ArrayList<Map>();
		Session session = sessionFactory.getCurrentSession();
        Connection con = session.connection();
        System.out.println("kode: " + kode);
        String sql = null;
        CallableStatement call; 
        try {
        	
        		sql = "{call bbtrxpengendalian.SETNO_RAKOR(?)}";
            	System.out.println("no rakor: " + map.get("no_rakor").toString());
            	call = con.prepareCall(sql);
            	call.setString(1, map.get("no_rakor").toString());
            	call.execute();
            	
            	sql = "{call bbtrxpengendalian.SETID_PLTU(?)}";
            	System.out.println("id pltu: " + map.get("id_pltu").toString());
            	call = con.prepareCall(sql);
            	call.setString(1, map.get("id_pltu").toString());
            	call.execute();
        	
            sql = "{? = call bbtrxpengendalian.SETRELATIONRAKOR(?,?)}";
            call = con.prepareCall(sql);
            call.registerOutParameter(1, OracleTypes.CURSOR);
            call.setString(2, kode);
            call.setString(3, map.get("crud").toString());
            call.execute();
            ResultSet rset = (ResultSet) call.getObject(1);
            lMap = CommonModule.convertResultsetToListLower(rset);
           }catch (Exception e) {
			e.printStackTrace();
		}
        
        return lMap;
	}
	
}
